/*
 * $RCSfile: DatabaseAction.java,v $$
 * $Revision: 1.1 $
 * $Date: 2013-12-15 $
 *
 * Copyright (C) 2008 Skin, Inc. All rights reserved.
 *
 * This software is the proprietary information of Skin, Inc.
 * Use is subject to license terms.
 */
package com.skin.webcat.action;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.skin.j2ee.action.BaseAction;
import com.skin.j2ee.annotation.UrlPattern;
import com.skin.j2ee.util.JsonUtil;
import com.skin.j2ee.util.Response;
import com.skin.util.HtmlUtil;
import com.skin.webcat.database.Table;
import com.skin.webcat.database.handler.TableHandler;
import com.skin.webcat.util.Jdbc;
import com.skin.webcat.util.Sql;
import com.skin.webcat.util.Webcat;
import com.skin.webcat.web.WebcatServlet;

/**
 * <p>Title: DatabaseAction</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * @author xuesong.net
 * @version 1.0
 */
public class DatabaseAction extends BaseAction {
    private static final Logger logger = LoggerFactory.getLogger(DatabaseAction.class);

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/database.html")
    public void tree() throws ServletException, IOException {
        this.forward("/template/webcat/database.jsp");
    }

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/database/list.html")
    public void list() throws ServletException, IOException {
        Connection connection = null;
        String name = this.getTrimString("name");

        try {
            connection = Webcat.getConnection(name);
            TableHandler tableHandler = new TableHandler(connection);
            List<String> databaseList = tableHandler.getDatabase(connection);

            this.setAttribute("name", name);
            this.setAttribute("databaseList", databaseList);
        }
        catch(Exception e) {
            logger.error(e.getMessage(), e);
        }
        finally {
            Jdbc.close(connection);
        }
        this.forward("/template/webcat/databaseList.jsp");
    }

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/database/create.html")
    public void create() throws ServletException, IOException {
        String name = this.getTrimString("name");
        String database = this.getTrimString("database");
        String charset = this.getTrimString("charset");
        String collate = this.getTrimString("collate");

        /**
         * create database if not exists [DB_NAME] default charset utf8 collate utf8_general_ci;
         * create database if not exists [DB_NAME] default character set gbk collate gbk_chinese_ci;
         */
        if(!Sql.isSqlIdentifier(database)) {
            JsonUtil.error(this.request, this.response, 500, "bad database name: " + database);
            return;
        }

        charset = Sql.filter(charset);
        StringBuilder buffer = new StringBuilder();
        buffer.append("create database ");
        buffer.append(database);
        
        if(charset.length() > 0) {
            buffer.append(" default charset ");
            buffer.append(charset);
            
            if(collate.length() > 0) {
                buffer.append(" collate ");
                buffer.append(collate);
            }
        }

        String sql = buffer.toString();
        WebcatServlet.execute(this.request, this.response, name, sql);
    }

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/database/rename.html")
    public void rename() throws ServletException, IOException {
        String name = this.getTrimString("name");
        String oldDatabase = this.getTrimString("oldDatabase");
        String newDatabase = this.getTrimString("newDatabase");

        if(!Sql.isSqlIdentifier(oldDatabase)) {
            JsonUtil.error(this.request, this.response, 500, "bad database name: " + oldDatabase);
            return;
        }

        if(!Sql.isSqlIdentifier(newDatabase)) {
            JsonUtil.error(this.request, this.response, 500, "bad database name: " + newDatabase);
            return;
        }
        String sql = "rename database " + oldDatabase + " " + newDatabase;
        WebcatServlet.execute(this.request, this.response, name, sql);
    }

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/database/drop.html")
    public void drop() throws ServletException, IOException {
        String name = this.getTrimString("name");
        String database = this.getTrimString("database");
        String sql = "drop database " + database;

        if(!Sql.isSqlIdentifier(database)) {
            JsonUtil.error(this.request, this.response, 500, "bad database name: " + database);
            return;
        }
        WebcatServlet.execute(this.request, this.response, name, sql);
    }

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/getConnectionXml.html")
    public void getConnectionXml() throws ServletException, IOException {
        String contextPapth = this.getContextPath();
        StringBuilder buffer = new StringBuilder("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
        buffer.append("<tree>\r\n");

        try {
            List<String> connections = Webcat.getConnectionList();

            for(String name : connections) {
                buffer.append("<treeNode");
                buffer.append(" icon=\"db.gif\"");
                buffer.append(" title=\"");
                buffer.append(name);
                buffer.append("\"");
                buffer.append(" href=\"");
                buffer.append(contextPapth);
                buffer.append("/webcat/database/list.html?name=");
                buffer.append(name);
                buffer.append("\"");
                buffer.append(" nodeXmlSrc=\"");
                buffer.append(contextPapth);
                buffer.append("/webcat/getDatabaseXml.html?name=");
                buffer.append(name);
                buffer.append("\"/>\r\n");
            }

            File file = new File(this.servletContext.getRealPath("/WEB-INF/sqls"));

            if(file.exists() && file.isDirectory()) {
                File[] files = file.listFiles();

                if(files != null && files.length > 0) {
                    for(File f : files) {
                        String name = f.getName().toLowerCase();

                        if(f.isFile() && name.endsWith(".sql")) {
                            String fileName = HtmlUtil.encode(f.getName());
                            buffer.append("<treeNode");
                            buffer.append(" icon=\"script.gif\"");
                            buffer.append(" title=\"");
                            buffer.append(fileName);
                            buffer.append("\"");
                            buffer.append(" href=\"");
                            buffer.append(contextPapth);
                            buffer.append("/webcat/sql/list.html?fileName=");
                            buffer.append(fileName);
                            buffer.append("&amp;type=TABLE\"");
                            buffer.append(" nodeXmlSrc=\"");
                            buffer.append(contextPapth);
                            buffer.append("/webcat/sql/getTableXml.html?fileName=");
                            buffer.append(fileName);
                            buffer.append("&amp;type=TABLE\"");
                            buffer.append("/>\r\n");
                        }
                    }
                }
            }
        }
        catch(Exception e) {
            logger.error(e.getMessage(), e);
        }
        buffer.append("</tree>");
        Response.write(this.request, this.response, "text/xml; charset=utf-8", buffer.toString());
    }

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/getDatabaseXml.html")
    public void getDatabaseXml() throws ServletException, IOException {
        Connection connection = null;
        String name = this.getTrimString("name");
        String contextPapth = this.getContextPath();
        StringBuilder buffer = new StringBuilder("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
        buffer.append("<tree>\r\n");

        try {
            connection = Webcat.getConnection(name);
            TableHandler tableHandler = new TableHandler(connection);
            List<String> list = tableHandler.getDatabase(connection);

            for(String database : list) {
                buffer.append("<treeNode");
                buffer.append(" icon=\"folder.gif\"");
                buffer.append(" title=\"");
                buffer.append(database);
                buffer.append("\"");
                buffer.append(" href=\"javascript:void(0)\">");

                buffer.append("<treeNode");
                buffer.append(" icon=\"table.gif\"");
                buffer.append(" title=\"Tables\"");
                buffer.append(" href=\"");
                buffer.append(contextPapth);
                buffer.append("/webcat/table/list.html?name=");
                buffer.append(name);
                buffer.append("&amp;database=");
                buffer.append(database);
                buffer.append("&amp;type=TABLE\"");
                buffer.append(" nodeXmlSrc=\"");
                buffer.append(contextPapth);
                buffer.append("/webcat/getTableXml.html?name=");
                buffer.append(name);
                buffer.append("&amp;database=");
                buffer.append(database);
                buffer.append("\"/>");

                buffer.append("<treeNode");
                buffer.append(" icon=\"table.gif\"");
                buffer.append(" title=\"Views\"");
                buffer.append(" href=\"");
                buffer.append(contextPapth);
                buffer.append("/webcat/tableList.html?name=");
                buffer.append(name);
                buffer.append("&amp;database=");
                buffer.append(database);
                buffer.append("&amp;type=VIEW\"");

                buffer.append(" nodeXmlSrc=\"");
                buffer.append(contextPapth);
                buffer.append("/webcat/getViewXml.html?name=");
                buffer.append(name);
                buffer.append("&amp;database=");
                buffer.append(database);
                buffer.append("\"/>");
                buffer.append("</treeNode>\r\n");
            }
        }
        catch(Exception e) {
            logger.error(e.getMessage(), e);
        }
        finally {
            Jdbc.close(connection);
        }
        buffer.append("</tree>");
        Response.write(this.request, this.response, "text/xml; charset=utf-8", buffer.toString());
    }

    /**
     * @throws IOException
     */
    @UrlPattern("/webcat/getTableXml.html")
    public void getTableXml() throws IOException {
        String name = this.getTrimString("name");
        String database = this.getTrimString("database");
        String xml = this.getTableXml(name, database, "TABLE");
        Response.write(this.request, this.response, "text/xml; charset=utf-8", xml);
    }

    /**
     * @throws IOException
     */
    @UrlPattern("/webcat/getViewXml.html")
    public void getViewXml() throws IOException {
        String name = this.getTrimString("name");
        String database = this.getTrimString("database");
        String xml = this.getTableXml(name, database, "VIEW");
        Response.write(this.request, this.response, "text/xml; charset=utf-8", xml);
    }

    /**
     * @param type
     * @throws IOException
     */
    private String getTableXml(String name, String database, String type) throws IOException {
        Connection connection = null;
        String contextPapth = this.getContextPath();
        StringBuilder buffer = new StringBuilder("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
        buffer.append("<tree>");

        try {
            connection = Webcat.getConnection(name, database);
            TableHandler tableHandler = new TableHandler(connection);
            List<Table> tableList = tableHandler.getTableList(null, null, "%", new String[]{type}, false);

            if(tableList != null && tableList.size() > 0) {
                for(Table table : tableList) {
                    buffer.append("<treeNode");
                    buffer.append(" title=\"");
                    buffer.append(HtmlUtil.encode(table.getTableName()));
                    buffer.append("\"");
                    buffer.append(" href=\"");
                    buffer.append(contextPapth);
                    buffer.append("/webcat/table/edit.html");
                    buffer.append("?name=");
                    buffer.append(HtmlUtil.encode(name));
                    buffer.append("&amp;database=");
                    buffer.append(HtmlUtil.encode(database));
                    buffer.append("&amp;tableName=");
                    buffer.append(HtmlUtil.encode(table.getTableName()));
                    buffer.append("\"/>");
                }
            }
        }
        catch(SQLException e) {
            logger.error(e.getMessage(), e);
        }
        finally {
            Jdbc.close(connection);
        }
        buffer.append("</tree>");
        return buffer.toString();
    }
}
